package com.feather.tenia.mssqlserver;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.feather.common.data.ColumnCollection;
import com.feather.common.data.simple.SimpleColumn;
import com.feather.tenia.Tenia;
import com.feather.tenia.adapter.ProcedureMetaAdapter;
import com.feather.tenia.adapter.TeniaAdapter;
import com.feather.tenia.adapter.util.CloseUtil;

/**
 * @author flogyin
 */
public class SqlserverProcedureMeta extends ProcedureMetaAdapter {
	private static final long	serialVersionUID	= 1L;
	private String				callName;

	@Override
	public void setName(String name) {
		if (name != null && name.charAt(name.length() - 2) == ';') {
			name = name.substring(0, name.length() - 2);
		}
		this.name = name;
	}

	@Override
	public String getPrintName() {
		return getCallName();
	}

	@Override
	public String getCallName() {
		if (this.callName == null) {
			StringBuilder builder = new StringBuilder();
			String catalog = getCatalog();
			if (catalog != null) {
				builder.append("[");
				builder.append(catalog);
				builder.append("].");
				String schema = getSchema();
				if (schema != null) {
					builder.append("[");
					builder.append(schema);
					builder.append("].");
				}
			}
			builder.append("[");
			builder.append(getName());
			builder.append("]");
			this.callName = builder.toString();
		}
		return this.callName;
	}

	@Override
	public void loadColumns(Tenia tenia) throws SQLException {
		ColumnCollection columns = getColumns();
		/*
		 * SELECT * FROM SYS.All_Objects where name like '%test_da' SELECT *
		 * FROM sysobjects where name like '%test_da' select * from
		 * sys.parameters where object_id = object_id('p_test_da')
		 * 
		 * SELECT t.name AS t_name, c.prec,c.scale,c.isoutparam,c.isnullable
		 * FROM syscolumns c LEFT JOIN systypes t ON c.xtype=t.xtype AND
		 * c.usertype=t.usertype-- and c.type=t.type WHERE id=(select id from
		 * sysobjects where name='p_test_da')
		 */
		String metaCatalog = getCatalog();
		String catprev = metaCatalog != null && metaCatalog.length() > 0 ? "[" + metaCatalog + "].[dbo]." : "";
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT t.name AS t_name");
		builder.append("      ,c.name");
		builder.append("      ,c.prec");
		builder.append("      ,c.scale");
		builder.append("      ,c.isoutparam");
		builder.append("      ,c.isnullable");
		builder.append(" FROM ");
		builder.append(catprev + "syscolumns c ");
		builder.append(" LEFT JOIN " + catprev + "systypes t ON c.xtype=t.xtype AND c.usertype=t.usertype ");
		builder.append(" WHERE id=(SELECT id FROM  " + catprev + "sysobjects WHERE name=?)");

		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		try {
			connection = tenia.getConnector().getConnection();
			statement = connection.prepareStatement(builder.toString());
			statement.setString(1, getName());

			int sequence = 1;
			rs = statement.executeQuery();
			while (rs.next()) {
				String name = rs.getString("name");
				String t_name = rs.getString("t_name");
				int prec = rs.getInt("prec");
				short scale = rs.getShort("scale");
				int isoutparam = rs.getInt("isoutparam");
				int isnullable = rs.getInt("isnullable");

				SimpleColumn column = new SimpleColumn();
				column.setCatalog(metaCatalog);

				if (isoutparam == 1) {
					column.setType(name != null && name.length() > 0 ? // function
					(short) DatabaseMetaData.procedureColumnOut
							: (short) DatabaseMetaData.procedureColumnReturn);
				} else if (isoutparam == 0) {
					column.setType((short) DatabaseMetaData.procedureColumnIn);
				}

				column.setName(name);
				column.setDataTypeName(t_name);
				column.setPrecision(prec);
				column.setLength(prec);
				column.setScale(scale);
				column.setRadix((short) 0);

				((TeniaAdapter) tenia).setColumnDataType(column);

				column.setOrdinal(sequence++);
				column.setDefault(null);
				column.setNullable(isnullable == 1);
				// column.setRemarks(rs.getString("REMARKS"));

				columns.add(column);
			}
		} finally {
			CloseUtil.close(rs, statement, connection);
		}
	}

	@Override
	protected boolean hasBracket() {
		return true;
	}
}
